package com.efounder.chat.db;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.efounder.chat.model.ApplyGroupNotice;
import com.efounder.chat.model.Group;
import com.efounder.mobilecomps.contacts.User;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;

/**
 * 群通知数据库管理
 */
public class GroupNoticeDBManager {
    private SQLiteDatabase db;
    private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS", Locale.getDefault());


    public GroupNoticeDBManager() {
        this.db = GetDBHelper.getInstance().getDb();
    }

    /**
     * 插入或更新群通知
     *
     * @param applyGroupNotice
     * @return
     */
    public long insertOrUpdate(ApplyGroupNotice applyGroupNotice) {
        ContentValues values = getGroupNoticeContentValues(applyGroupNotice);
        return this.db.insertWithOnConflict("GroupNotice", null, values, SQLiteDatabase.CONFLICT_REPLACE);

    }

    /**
     * 插入或更新群通知
     */
    public void insertOrUpdate(List<ApplyGroupNotice> list) {
        this.db.beginTransaction();
        for (Iterator<ApplyGroupNotice> localIterator = list.iterator(); localIterator
                .hasNext(); ) {
            ApplyGroupNotice applyGroupNotice = (ApplyGroupNotice) localIterator.next();
            insertOrUpdate(applyGroupNotice);
        }
        this.db.setTransactionSuccessful();
        this.db.endTransaction();
    }

    /**
     * 设置所有的群通知未读为已读
     */
    public void setAllIsRead() {
        String sql = "update  GroupNotice  set  isread = 1 ";
        db.execSQL(sql);
    }

    /**
     * 通过id查询群通知
     *
     * @param id
     * @return
     */
    public ApplyGroupNotice queryGroupNoticeById(int id, int groupId) {
        String sql = "select * from GroupNotice,user,groups where user.userId=" + id
                + " and groups.groupId =" + groupId + "   and groups.groupId = GroupNotice.groupId " +
                "and GroupNotice.userId = user.userId and GroupNotice.enable =1 ;";
        List<?> list = query(sql);
        return ((list.size() == 0) ? null : (ApplyGroupNotice) list.get(0));

    }

    /**
     * 查询所有群通知
     * 每次查询30条
     *
     * @return
     */
    public List<ApplyGroupNotice> queryAllGroupNotices(int loadedCount) {
        String sql = "select User.userId,User.nickName,User.Avatar,groups.groupId,groups.groupName," +
                "GroupNotice.time,GroupNotice.state,GroupNotice.isRead,GroupNotice.leave_message from GroupNotice,User,Groups" +
                " where User.userId=GroupNotice.userId  and Groups.groupId = GroupNotice.groupId" +
                " and GroupNotice.enable =1  order by GroupNotice.state asc,GroupNotice.time desc Limit 20 offset " + loadedCount + ";";
        List<ApplyGroupNotice> list = query(sql);
        return list;

    }

    /**
     * 查询所有未读的群通知
     *
     * @return
     */
    public int queryAllGroupNoticeUnRead() {
        int count = 0;
        String sql = "select count(*) as count from GroupNotice where isread = 0 " + ";";
        Cursor cursor = this.db.rawQuery(sql, null);

        while (cursor.moveToNext()) {
            count = cursor.getInt(cursor.getColumnIndex("count"));
        }

        cursor.close();
        return count;

    }

    /**
     * 更新群通知(单个)
     *
     * @param applyGroupNotice
     * @return
     */
    public int update(ApplyGroupNotice applyGroupNotice) {
        ContentValues values = getGroupNoticeContentValues(applyGroupNotice);
        String whereClause = "userId=? and groupId = ?";
        String[] whereArgs = {String.valueOf(applyGroupNotice.getUserId()), String.valueOf(applyGroupNotice.getGroupId())};
        return this.db.update("GroupNotice", values, whereClause, whereArgs);
    }

    /**
     * 更新群通知(list)
     *
     * @param list
     * @return
     */
    public void update(List<ApplyGroupNotice> list) {
        this.db.beginTransaction();
        for (Iterator<ApplyGroupNotice> localIterator = list.iterator(); localIterator
                .hasNext(); ) {
            ApplyGroupNotice applyGroupNotice = (ApplyGroupNotice) localIterator.next();
            update(applyGroupNotice);
        }
        this.db.setTransactionSuccessful();
        this.db.endTransaction();
    }

    /**
     * 删除群通知（单个）
     *
     * @param
     * @return
     */
    public void delete(int userId, int groupId) {

//        this.db.execSQL("update GroupNotice set enable=0 where userId =" + applyGroupNotice.getUserId() +
//                " and groupId =" + applyGroupNotice.getGroupId() + ";");
        this.db.execSQL("delete from GroupNotice  where userId =" + userId + " and groupId =" + groupId + ";");
    }


    /**
     * 删除群通知（多个）
     *
     * @param list
     */
    public void delete(List<ApplyGroupNotice> list) {
        this.db.beginTransaction();
        for (Iterator<ApplyGroupNotice> localIterator = list.iterator(); localIterator
                .hasNext(); ) {
            ApplyGroupNotice applyGroupNotice = (ApplyGroupNotice) localIterator.next();
            delete(applyGroupNotice.getUserId(), applyGroupNotice.getGroupId());
        }
        this.db.setTransactionSuccessful();
        this.db.endTransaction();
    }

    public List<ApplyGroupNotice> query(String sql) {
        return query(sql, null);
    }

    public List<ApplyGroupNotice> query(String sql, String[] selectionArgs) {
        List<ApplyGroupNotice> list = new ArrayList<ApplyGroupNotice>();
        Cursor cursor = this.db.rawQuery(sql, selectionArgs);
        while (cursor.moveToNext()) {
            User user = new User();
            ApplyGroupNotice applyGroupNotice = new ApplyGroupNotice();
            Group group = new Group();
            user.setId(cursor.getInt(cursor.getColumnIndex("userId")));
            //user.setName(cursor.getString(cursor.getColumnIndex("name")));
            user.setNickName(cursor.getString(cursor.getColumnIndex("nickName")));
            // user.setSex(cursor.getString(cursor.getColumnIndex("sex")));
            // user.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
            //user.setMobilePhone(cursor.getString(cursor.getColumnIndex("mobilephone")));
            user.setAvatar(cursor.getString(cursor.getColumnIndex("avatar")));
            //user.setSigNature(cursor.getString(cursor.getColumnIndex("sigNature")));
            //user.setEmail(cursor.getString(cursor.getColumnIndex("email")));
            //user.setDeptId(cursor.getInt(cursor.getColumnIndex("deptId")));
            // user.setType(cursor.getInt(cursor.getColumnIndex("type")));

            group.setGroupId(cursor.getInt(cursor.getColumnIndex("groupId")));
            group.setGroupName(cursor.getString(cursor.getColumnIndex("groupName")));
            // group.setCreateId(cursor.getInt(cursor.getColumnIndex("createUserId")));
            applyGroupNotice.setState(cursor.getInt(cursor.getColumnIndex("state")));
            applyGroupNotice.setRead(cursor.getInt(cursor.getColumnIndex("isRead")) == 1 ? true : false);
            applyGroupNotice.setUserId(cursor.getInt(cursor.getColumnIndex("userId")));
            applyGroupNotice.setGroupId(cursor.getInt(cursor.getColumnIndex("groupId")));
            applyGroupNotice.setLeaveMessage(cursor.getString(cursor.getColumnIndex("leave_message")));
            applyGroupNotice.setUser(user);
            applyGroupNotice.setGroup(group);


            try {
                Date date = this.dateFormat.parse(cursor.getString(cursor.getColumnIndex("time")));
                applyGroupNotice.setTime(date.getTime());
            } catch (java.text.ParseException e) {
                e.printStackTrace();
            }

            list.add(applyGroupNotice);
        }

        cursor.close();
        return list;
    }

    private ContentValues getGroupNoticeContentValues(ApplyGroupNotice applyGroupUser) {
        ContentValues values = new ContentValues();
        values.put("userId", applyGroupUser.getUserId());
        values.put("groupId", applyGroupUser.getGroupId());
        values.put("state", applyGroupUser.getState());
        values.put("time", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS",
                Locale.getDefault()).format(new Date(applyGroupUser.getTime())));
        values.put("isRead", applyGroupUser.getRead() == true ? 1 : 0);
        values.put("leave_message", applyGroupUser.getLeaveMessage() == null ? "" : applyGroupUser.getLeaveMessage());
        values.put("enable", 1);
        return values;
    }


    /**
     * 关闭数据库
     */
    public void close() {
        this.db.close();
    }
}
